package org.hackystat.devcathlon.ui;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/**
 * Devcathlon's Derby Client driver class is responsible for 
 * the creation and initialization of Devcathlon's database
 * connections with Derby's Network Server.
 * 
 * @author anthony.m.du
 *
 */
public class DevcathlonDerbyClient {
  // Derby driver.
  private String driver = "org.apache.derby.jdbc.ClientDriver";
  // The default path of created db's reside in the same 
  //  place where the Network Server started.
  private String dbPath = System.getProperty("user.home") + "/.hackystat/devcathlon/db/";
  // Database name.
  private String dbName = "devcathlonDB";
  // Database host path.
  private String dbHostPath = "//localhost:1527/";
  // Connection URL.
  private String connectionURL = "jdbc:derby:" + dbHostPath + dbPath + dbName + ";create=true";
  
  // DB connection.
  protected Connection conn = null;
  // SQL statement.
  protected Statement stmt = null;
  // SQL query statement.
  protected String sqlQuery = null;
  
  // SQL script file path.
  private String scriptFilePath = 
                          "src/org/hackystat/devcathlon/engine/db/create-devcathlon.sql";
  
  /**
   * DevcathlonDerbyClient class constructor.
   */
  public DevcathlonDerbyClient() {
    // empty constructor
  }
  
  /**
   * Loads the Derby driver and creates the database.  
   */
  public void start() {
    System.out.println("Database initialization. Derby client started");
    /*
     * Load the Derby driver.
     * Catch an error and suggest a CLASSPATH problem
     */
    try {
      Class.forName(driver);
      System.out.println(driver + " loaded. ");
    } 
    catch (java.lang.ClassNotFoundException e) {
      System.err.print("ClassNotFoundException: ");
      System.err.println(e.getMessage());
      System.out.println("\n >>> Please check your CLASSPATH variable <<< \n");
      System.out.println("Derby client ending");
      // System.exit(0);
    }
    catch (RuntimeException re) {
      System.err.println("Something occured while attempting to initialize the DB...");
      re.printStackTrace();
    }
    /*
     * Begin the Derby Network Server communication.
     * - establishes connection to database
     * - creates database
     */
    try {
      conn = DriverManager.getConnection(connectionURL);
      System.out.println("Connected to database " + dbName);
      stmt = conn.createStatement();
      /* create tables here */
      executeSQLFile(stmt, 
          new File(scriptFilePath));
      stmt.close();
      conn.close();
      System.out.println("Closed connection");
    } 
    catch (SQLException se) {
      handleException(se);
    }
    
    System.out.println("Database initialized. Derby client ending.");
  }
  
  /**
   * Executes any valid SQL script file.
   * @param stmt The statement object.
   * @param file The SQL script file.
   */
  private void executeSQLFile(Statement stmt, File file) {
    try {
      Scanner scan = new Scanner(file);
      sqlQuery = "";
      
      while (scan.hasNextLine()) {
        String line = scan.nextLine();
        // Skip comments and empty lines
        if (line.length() > 0 
            && line.charAt(0) == '-'  
            || line.length() == 0) {
          continue;
        }
        // Append to final sql call.
        sqlQuery += " " + line;
        // Check for complete command (line ending with ';')
        if (sqlQuery.charAt(sqlQuery.length() - 1) == ';') {
          sqlQuery = sqlQuery.replace(';', ' ');
          try {
            // System.out.println(sqlQuery);
            stmt.execute(sqlQuery);
          }
          catch (SQLException se) {
            handleException(se);
          }
          sqlQuery = "";
        }
      }
    } 
    catch (FileNotFoundException e) {
      System.out.println(">> Please specify an existing SQL file. << ");
      e.printStackTrace();
    }
  }
  
  /**
   * Handles printout of SQLException errors/warnings.
   * @param se The SQLException error/warning.
   */
  private void handleException(SQLException se) {
    SQLException sqlException = se;
    while (sqlException != null) {
      String SQLState = sqlException.getSQLState(); 
      String SQLMessage = sqlException.getMessage();
      System.out.println("Error = " + SQLState);
      System.out.println(SQLMessage);
      sqlException = sqlException.getNextException();
    }
  }
}
